encounter

Table: encounter

The encounter table records interactions or visits between two participants (p1 and p2) in the system.
It captures details about the encounter, including type, timestamps, location, and data entry information.

---

Columns

Column Name	Data Type	Constraints	Description
encounterId	int(11)	NOT NULL	Unique identifier for the encounter
p1id	int(11)	NOT NULL	Participant 1 ID (references idmapper)
p2id	int(11)	NOT NULL	Participant 2 ID (references idmapper)
createdByUserId	int(11)	NOT NULL	User who recorded the encounter
dataEntrySource	varchar(20)	NOT NULL	Source of data entry (e.g., manual, system)
dateEncounterEnd	datetime	NULL	Timestamp when the encounter ended
dateEncounterEntered	datetime	NOT NULL	Timestamp when the encounter was entered into the system
dateEncounterStart	datetime	NULL	Timestamp when the encounter started
detail	varchar(255)	NULL	Additional notes or details about the encounter
encounterType	varchar(30)	NOT NULL	Type of encounter (e.g., visit, call)
locationId	varchar(12)	NULL	Identifier of the location where the encounter occurred

---

Indexes

1. PRIMARY - Composite primary key on (encounterId, p1id, p2id)
2. encounter_p2id_idmapper_mappedId_FK - Index on p2id
3. encounter_p1id_idmapper_mappedId_FK - Index on p1id
4. encounter_createdByUserId_user_mappedId_FK - Index on createdByUserId

---

Foreign Key Relations

1. encounter_createdByUserId_user_mappedId_FK

   • createdByUserId → user.mappedId
   • Tracks which user recorded the encounter

2. encounter_p1id_idmapper_mappedId_FK

   • p1id → idmapper.mappedId
   • Links participant 1 to the ID mapping system

3. encounter_p2id_idmapper_mappedId_FK

   • p2id → idmapper.mappedId
   • Links participant 2 to the ID mapping system

---

Usage Notes

• Captures interactions between two participants for tracking and reporting.
• dataEntrySource helps differentiate manual vs automated entries.
• Composite primary key ensures uniqueness for each encounter between two participants.
• detail field allows storing any extra notes about the encounter.
• Ensures referential integrity with user and idmapper tables.